﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ZhimeiSoft.CharmManager.Server.DAL
{
    public class ToDoListDAL_Const
    {
        /// <summary>
        /// 插入待办事项
        /// </summary>
        public static readonly string SQL_INSERT_TODOLIST = @"INSERT INTO TODOLIST (TODODECRIPTION, TODOSTARTTIME,TODOENDTIME,TODOCOMPLETIONRATE
           ,TODOSTATE,TODOMEMO,EMPLOYEEID) VALUES(@TODODECRIPTION, @TODOSTARTTIME, @TODOENDTIME, @TODOCOMPLETIONRATE, @TODOSTATE, @TODOMEMO, @EMPLOYEEID)";
        /// <summary>
        /// 更新代办事项
        /// </summary>
        public static readonly string SQL_UPDATE_TODOLIST = @"UPDATE TODOLIST SET TODODECRIPTION = @TODODECRIPTION, TODOSTARTTIME = @TODOSTARTTIME, 
        TODOENDTIME = @TODOENDTIME, TODOCOMPLETIONRATE = @TODOCOMPLETIONRATE, TODOSTATE = @TODOSTATE, TODOMEMO = @TODOMEMO ,EMPLOYEEID = @EMPLOYEEID
        WHERE TODOLISTID = @TODOLISTID";
        /// <summary>
        /// 删除待办事项
        /// </summary>
        public static readonly string SQL_DELETE_TODOLIST = @"DELETE FROM TODOLIST WHERE TODOLISTID = @TODOLISTID";
        /// <summary>
        /// 更新预约代办事项
        /// </summary>
        public static readonly string SQL_INSERT_RESVERATIONTODOLIST = @"INSERT INTO RESVERATIONTODOLIST(TODOLISTID, RESERVATIONID) VALUES (@TODOLISTID, @RESERVATIONID)";
        /// <summary>
        /// 删除预约待办事项
        /// </summary>
        public static readonly string SQL_DELETE_RESVERATIONTODOLIST = "DELETE FROM RESVERATIONTODOLIST WHERE TODOLISTID = @TODOLISTID AND RESERVATIONID = @RESERVATIONID";
        /// <summary>
        /// 查询代办事项
        /// </summary>
        public static readonly string SQL_QUERY_TODOLIST = @"SELECT TODOLISTID,TODODECRIPTION,TODOSTARTTIME,TODOENDTIME,TODOCOMPLETIONRATE
                ,TODOSTATE,TODOMEMO,EMPLOYEEID FROM TODOLIST WHERE TODOSTATE = @TODOSTATE";

        public static readonly string SQL_QUERY_ALLTODOLIST = @"SELECT TODOLIST.*, EMPLOYEEINFO.ENAME AS EMPLOYEENAME  FROM TODOLIST LEFT OUTER JOIN EMPLOYEEINFO  ON TODOLIST.EMPLOYEEID = EMPLOYEEINFO.EMPLOYEEID
                WHERE 1=1 ";

        /// <summary>
        /// 查询代办事项ID
        /// </summary>
        public static readonly string SQL_QUERY_TODOLISTID = @"SELECT TODOLISTID FROM TODOLIST WHERE TODODECRIPTION = @TODODECRIPTION AND TODOSTARTTIME = @TODOSTARTTIME 
            AND TODOENDTIME = @TODOENDTIME AND TODOCOMPLETIONRATE = @TODOCOMPLETIONRATE AND TODOSTATE = @TODOSTATE AND TODOMEMO = @TODOMEMO AND EMPLOYEEID = @EMPLOYEEID";
        /// <summary>
        /// 根据开始时间过滤代办事项
        /// </summary>
        public static readonly string SQL_QUWEY_TODOLIST_STARTTIME = @" SELECT A.*, B.ECODE, B.ENAME FROM TODOLIST A, EMPLOYEEINFO B
                        WHERE A.EMPLOYEEID = B.EMPLOYEEID AND YEAR(A.TODOSTARTTIME) = @STARTYEAR AND MONTH(A.TODOSTARTTIME) = @STARTMONTH AND DAY(A.TODOSTARTTIME) = @STARTDAY";
        /// <summary>
        /// 根据开始时间和状态过滤待办事项
        /// </summary>
        public static readonly string SQL_QUWEY_TODOLIST_STARTTIME_STATE = @"SELECT A.*, B.ECODE, B.ENAME FROM TODOLIST A, EMPLOYEEINFO B
                WHERE A.EMPLOYEEID = B.EMPLOYEEID AND YEAR(A.TODOSTARTTIME) = @STARTYEAR AND MONTH(A.TODOSTARTTIME) = @STARTMONTH AND DAY(A.TODOSTARTTIME) = @STARTDAY AND A.TODOSTATE = @ESTATE";

        public static readonly string SQL_UPDATE_MARKCOMPLETED = "UPDATE TODOLIST SET TODOCOMPLETIONRATE=100, TODOSTATE='T0V' WHERE TODOLISTID=@TODOLISTID";

        /// <summary>
        /// 
        /// </summary>
        public static readonly string SQL_UPDATE_UPDATEEXPIREDTODOSTATE = @"UPDATE TODOLIST SET TODOSTATE='T0T' WHERE YEAR(TODOENDTIME) * 10000 + MONTH(TODOENDTIME) * 100 + DAY(TODOENDTIME) 
            < YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE()) AND TODOSTATE <> 'T0V'";

        public static readonly string SQL_UPDATE_UPDATEEXPIREDTODOSTATE_STARTNOW = @"UPDATE TODOLIST SET TODOSTATE='T0I' WHERE YEAR(TODOSTARTTIME) * 10000 + MONTH(TODOSTARTTIME) * 100 + DAY(TODOSTARTTIME)
            = YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE()) AND TODOSTATE <> 'T0V'";

        public static readonly string SQL_QUERY_RESVERATIONTODOLIST = "SELECT * FROM RESVERATIONTODOLIST WHERE RESERVATIONID = @RESERVATIONID";

        public static readonly string SQL_QUERY_TODOLIST_TODOLISTID = "SELECT * FROM TODOLIST WHERE TODOLISTID = @TODOLISTID";

        public static readonly string SQL_QUERY_RESVERATIONTODOLIST_RESRVATION = @"SELECT B.* FROM RESVERATIONTODOLIST A, RESERVATION  B  WHERE A.TODOLISTID = @TODOLISTID AND B.RESERVATIONID = A.RESERVATIONID";
    }
}
